package test;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

public class WorkshopDataAccess {
	
	public List<Workshop> getWorkshopsForSelectedConference(int confId)
	throws SQLException, IOException {
Connection conn = null;
CallableStatement cstmt = null;
List<Workshop> workshopDetails = new ArrayList<Workshop>();
 
ResultSet rset;
try {
	Class.forName("oracle.jdbc.driver.OracleDriver");
	String user, pass;
	user = "skakodkar";
	pass = "p73505";
	conn = DriverManager.getConnection("jdbc:oracle:thin:" + user + "/"
			+ pass + "@oraserver.cs.luc.edu:1521:cs");
	cstmt = conn.prepareCall
	("{?,?,?,?,?,? = call get_workshop_details(?)}");
	
	cstmt.setInt(8,confId);
	cstmt.registerOutParameter(1, Types.VARCHAR);
	cstmt.registerOutParameter(2, Types.INTEGER);
	cstmt.registerOutParameter(3,Types.VARCHAR);
	cstmt.registerOutParameter(4,Types.VARCHAR);
	cstmt.registerOutParameter(5,Types.VARCHAR);
	cstmt.registerOutParameter(6,Types.VARCHAR);
	cstmt.registerOutParameter(7,Types.VARCHAR);
	rset = cstmt.executeQuery();
	while (rset.next()) {
		Workshop wshop = new Workshop();
		wshop.setWorkshopTitle(rset.getString(1));
		wshop.setSpeakerId(rset.getInt(2));
		wshop.setWorkshopDescr(rset.getString(3));
		wshop.setLevelOfExperience(rset.getString(4));
		wshop.setAge(rset.getString(5));
		wshop.setStartTime(rset.getString(6));
		wshop.setEndTime(rset.getString(7));
		workshopDetails.add(wshop);
	}

} catch (Exception e) {
	e.printStackTrace();
} finally {
	conn.close();
}
return workshopDetails;

}

}
